﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.Data.Common;
using System.IO;

namespace csexplorer
{
    public class SqliteFileInfoDao : IFileInfoDao
    {
        private string connectionString;

        public void DbOpen(string dbName)
        {
            connectionString = "Data Source="
                + dbName + ";Version=3;New=True;Compress=True;";
        }

        public string ConnectionString
        {
            get
            {
                return connectionString;
            }
            set
            {
                connectionString = value;
            }
        }

        public void SaveFileInfo(List<FileInformation> fileInfo)
        {
            SQLiteConnection cnn = new SQLiteConnection(ConnectionString);
            SQLiteDataAdapter da = new SQLiteDataAdapter("SELECT * FROM Files", cnn);

            using (DbCommand cmd = cnn.CreateCommand())
            {
                foreach (FileInformation fi in fileInfo)
                {
                    cmd.CommandText = "INSERT INTO Files(FileID, Name) VALUES(?,?)";
                    DbParameter par = cmd.CreateParameter();
                    par.SourceColumn = "FileID";
                    par.Value = fi.FileId;
                    cmd.Parameters.Add(par);

                    par = cmd.CreateParameter();
                    par.SourceColumn = "Name";
                    par.Value = fi.Name;
                    cmd.Parameters.Add(par);

                    cnn.Open();
                    cmd.ExecuteNonQuery();
                }

                cnn.Close();
            }
        }

        public List<FileInformation> GetByName(string name)
        {
            List<FileInformation> ret = new List<FileInformation>();

            SQLiteConnection cnn = new SQLiteConnection(ConnectionString);
            string findField = "Name";
            string commStr = string.Format("SELECT * FROM Files WHERE {0} = {1}", findField, name);
            SQLiteDataAdapter da = new SQLiteDataAdapter(commStr, cnn);

            using (SQLiteCommand cmd = cnn.CreateCommand())
            {
                SQLiteDataReader reader = cmd.ExecuteReader();

                FileInformation fi;
                while (reader.Read())
                {
                    fi = new FileInformation(reader["Name"].ToString());
                    ret.Add(fi);
                }
            }

            return ret;
        }

        public bool DbExists(string dbName)
        {
            return File.Exists(dbName);
        }

        public void DbCreate(string dbName)
        {
            // create and open db
            SQLiteConnection.CreateFile(dbName);
            DbOpen(dbName);

            // create table
            string cmdStr = "CREATE TABLE [Files] ([FileID] INTEGER  NOT NULL PRIMARY KEY,[Name] VARCHAR(255)  NULL)";

            try
            {
                ExecuteNonQuery(cmdStr);
            }
            catch (Exception ex)
            {
                
            }
        }

        private void ExecuteNonQuery(string commStr)
        {
            using (SQLiteConnection cnn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand cmd = cnn.CreateCommand())
                {
                    cmd.CommandText = commStr;
                    cmd.CommandType = CommandType.Text;
                    cnn.Open();
                    cmd.ExecuteNonQuery();
                    cnn.Close();
                }
            }
        }
    }
}
